"""
Case Type   : 功能测试
Case Name   : timecapsule timestamp闪回查询因过旧被回收版本，合理报错
Create At   : 2022/11/21
Owner       : @zou_jialiang0501208402
Description :
            1、设置undo旧版本保留时间为0
            2、创建表
            3、在表中插入数据
            4、查询创建的表
            5、删除表数据
            6、闪回查询表数据并将结果插入到表
            7、清理环境
Expect      :
            1、成功
            2、成功
            3、成功
            4、成功
            5、成功
            6、失败
            7、成功
History     :
"""

import os
import re
import time
import unittest
from yat.test import Node
from yat.test import macro

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class SQL(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.Constant = Constant()
        self.pri_node = Node('PrimaryDbUser')
        self.env_path = macro.DB_ENV_PATH
        self.t_name1 = 't_select_into_0047_1'
        self.t_name2 = 't_select_into_0047_2'
        self.config_undo = 'undo_retention_time'

    def test_timecapsule_table(self):
        step1 = '--step1:设置undo旧版本保留时间为0;expect:成功--'
        self.log.info(step1)
        msg = self.primary_sh.execute_gsguc('reload',
                                            self.Constant.GSGUC_SUCCESS_MSG,
                                            f'{self.config_undo} = 0')
        self.assertTrue(msg, '执行失败' + step1)

        step2 = '--step2:创建表;expect:成功--'
        self.log.info(step2)
        sql_cmd = f'drop table if exists {self.t_name1};' \
                  f'create table {self.t_name1} (' \
                  f'idx integer, ' \
                  f'snaptime timestamp, ' \
                  f'snapcsn bigint, ' \
                  f'timeDesc character(100)) ' \
                  f'with (storage_type=ustore);'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.TABLE_DROP_SUCCESS, result,
                      '执行失败' + step2)
        self.assertIn(self.Constant.CREATE_TABLE_SUCCESS, result,
                      "执行失败" + step2)

        step3 = '--step3:在表中插入数据;expect:成功--'
        self.log.info(step3)
        sql_cmd = f"insert into {self.t_name1} " \
                  f"select 1, now(),int8in(xidout(next_csn)), 'time1' " \
                  f"from gs_get_next_xid_csn();"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step3)
        time.sleep(5)
        sql_cmd = f"insert into {self.t_name1} " \
                  f"select 2, now(),int8in(xidout(next_csn)), 'time2' " \
                  f"from gs_get_next_xid_csn();"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step3)
        time.sleep(5)
        sql_cmd = f"insert into {self.t_name1} " \
                  f"select 3, now(),int8in(xidout(next_csn)), 'time3' " \
                  f"from gs_get_next_xid_csn();"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step3)
        time.sleep(5)
        sql_cmd = f"insert into {self.t_name1} " \
                  f"select 4, now(),int8in(xidout(next_csn)), 'time4' " \
                  f"from gs_get_next_xid_csn();"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.INSERT_SUCCESS_MSG, result,
                      '执行失败' + step3)

        step4 = '--step4:查询创建的表;expect:成功--'
        self.log.info(step4)
        sql_cmd = f'select snaptime from {self.t_name1} where idx = 4;'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertRegex(result, '\d+-\d+-\d+ \d+:\d+:\d+\.\d+',
                         '执行失败' + step4)
        t_time = re.findall('\d+-\d+-\d+ \d+:\d+:\d+\.\d+',
                            result)[0]

        step5 = '--step5:删除表数据;expect:成功--'
        self.log.info(step5)
        sql_cmd = f'delete from {self.t_name1};'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.DELETE_SUCCESS_MSG, result,
                      '执行失败' + step5)

        step6 = '--step6:闪回查询表数据并将结果插入到表;expect:失败--'
        self.log.info(step6)
        sql_cmd = f"select * into {self.t_name2} " \
                  f"from {self.t_name1} " \
                  f"timecapsule timestamp " \
                  f"to_timestamp('{t_time}','YYYY-MM-DD HH24:MI:SS.FF');"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('timecapsule feature is not enabled', result,
                      '执行失败' + step6)

    def tearDown(self):
        step7 = '--step7:清理环境;expect:成功--'
        self.log.info(step7)
        sql_cmd = f'drop table if exists {self.t_name1};'
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.Constant.DROP_TABLE_SUCCESS, result,
                      '执行失败' + step7)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')